package ua.epam.muzalevskiy.traction.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import org.apache.log4j.Logger;
import ua.epam.muzalevskiy.traction.dao.exceptions.NotExistsParameterException;
import ua.epam.muzalevskiy.traction.dao.exceptions.NotUniqueParameterException;
import ua.epam.muzalevskiy.traction.user.AuthorizedUser;
import ua.epam.muzalevskiy.traction.user.User;

/**
 *
 * @author Muzalevskiy Vitaliy
 */
public class UserDaoImplementation implements UserDao {
    
    private static final Logger LOGGER =
            Logger.getLogger(UserDaoImplementation.class);
    private final ConnectionFactory factory =
            ConnectionFactoryFactory.newConnectionFactory();
    
    /** Query for inserting new user to DataBase.*/
    public static final String ADD_USER_SQL = "INSERT INTO traction.user "
            + "VALUES( null, ?, ?, ?)";
    
    /** Query to select all users from DataBase.*/
    public static final String SELECT_ALL_SQL = "SELECT * FROM traction.user";
    
    /** Query to select user with concrete ID value.*/
    public static final String SELECT_BY_ID_SQL = "SELECT * FROM "
            + "traction.user WHERE id = ?";
    
    /** Query to select user with concrete login value.*/
    public static final String SELECT_BY_NAME_SQL = "SELECT * FROM "
            + "traction.user WHERE user_name = ?";
    
    /** Query to select user with concrete email value.*/
    public static final String SELECT_BY_EMAIL_SQL = "SELECT * FROM "
            + "traction.user WHERE email = ?";
    
    /** Query to delete user with concrete ID value.*/
    public static final String DELETE_BY_ID_SQL = "DELETE FROM traction.user "
            + "WHERE id = ?";
    
    /** Query to delete administrator with concrete login and password value.*/
    public static final String SELECT_USER_SQL = "SELECT * FROM traction.user "
            + "WHERE user_name = ? AND user_password = ?";
       
    public static final String ATTRIBUTE_ID = "id";
    public static final String ATTRIBUTE_USER_NAME = "user_name";
    public static final String ATTRIBUTE_USER_PASSWORD = "user_password";
    public static final String ATTRIBUTE_EMAIL = "email";

    static {
        ConnectionFactoryFactory.setType(ConnectionFactoryFactory.FactoryType.C3P0);
    }

    /**
     * Method need to save information about new user in DataBase.
     * 
     * @param query <code>PreparedStatement</code> value that include all
     *      information that need to be saved in DataBase.
     * @throws SQLException if there was some problems with Query.
     */
    private void doInsert(PreparedStatement query, String name,
            String password, String email) throws SQLException {
        query.setString(1, name);
        query.setString(2, password);
        query.setString(3, email);
        query.executeUpdate();
        LOGGER.info("New User info was put to DataBase! (user_name = " + name + ")");
    }

    /**
     * Method for getting information about user from DataBase.
     * 
     * @param result <code>ResultSet</code> value of concrete query.
     * @return <code>User</code> value.
     * @throws SQLException if there was some problems with Query.
     */
    private AuthorizedUser createUser(ResultSet result) throws SQLException {
        int id = result.getInt(ATTRIBUTE_ID);
        String name = result.getString(ATTRIBUTE_USER_NAME);
        String password = result.getString(ATTRIBUTE_USER_PASSWORD);
        String email = result.getString(ATTRIBUTE_EMAIL);
        LOGGER.debug("User info was taken from DataBase! (user_name = " + name + ")");
        return new AuthorizedUser(id, name, password, email);
    }

    @Override
    public void addUser(User user) throws NotUniqueParameterException {
        try {
            Connection myConnection = null;
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection.prepareStatement(ADD_USER_SQL,
                        Statement.RETURN_GENERATED_KEYS);

                myConnection.setAutoCommit(false);
                if (selectUserByName(user.getName()) != null) {
                    throw new NotUniqueParameterException();
                }
                if (selectUserByEmail(user.getEmail()) != null) {
                    throw new NotUniqueParameterException();
                }
                try {
                    doInsert(query, user.getName(), user.getPassword(),
                            user.getEmail());
                    ResultSet resSet = query.getGeneratedKeys();
                    if (resSet.next()) {
                        LOGGER.info("ID of inserted user "
                                + new Integer(resSet.getInt(1)).toString());
                    }
                    query.close();
                    myConnection.commit();
                } catch (SQLException ex) {
                    LOGGER.warn(ex.getMessage());
                    LOGGER.info("Trtansaction is rolled back!");
                    myConnection.rollback();
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
    }

    @Override
    public List<User> selectAll() {
        List<User> usersList = new LinkedList();
        try {
            Connection myConnection = null;
            try {
                myConnection = factory.newConnection();
                Statement statement = myConnection.createStatement();
                ResultSet result = statement.executeQuery(SELECT_ALL_SQL);
                while (result.next()) {
                    AuthorizedUser user = createUser(result);
                    usersList.add(user);
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return usersList;
    }

    @Override
    public AuthorizedUser selectUserByID(int userID) {
        AuthorizedUser user = null;
        Connection myConnection = null;
        try {
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(SELECT_BY_ID_SQL);
                query.setInt(1, userID);
                ResultSet result = query.executeQuery();
                while (result.next()) {
                    user = createUser(result);
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return user;
    }

    @Override
    public void deleteUserByID(int userID) throws NotExistsParameterException {
        try {
            Connection myConnection = null;
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(DELETE_BY_ID_SQL);
                myConnection.setAutoCommit(false);
                AuthorizedUser user = selectUserByID(userID);
                if (user == null) {
                    throw new NotExistsParameterException();
                }
                query.setInt(1, userID);
                query.executeUpdate();
                query.close();
                myConnection.commit();
                LOGGER.info("User info was cleared from DataBase! (userID = "
                        + userID + ")");
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
    }

    @Override
    public AuthorizedUser getUser(String userName, String userPassword)
            throws NotExistsParameterException {
        AuthorizedUser user = null;
        try {
            Connection myConnection = null;
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(SELECT_USER_SQL);
                query.setString(1, userName);
                query.setString(2, userPassword);
                ResultSet result = query.executeQuery();
                while (result.next()) {
                    user = createUser(result);
                }
                if (user == null) {
                    throw new NotExistsParameterException();
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return user;
    }

    @Override
    public AuthorizedUser selectUserByName(String userName) {
        AuthorizedUser user = null;
        try {
            Connection myConnection = null;
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(SELECT_BY_NAME_SQL);
                query.setString(1, userName);
                ResultSet result = query.executeQuery();
                while (result.next()) {
                    user = createUser(result);
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return user;
    }

    @Override
    public AuthorizedUser selectUserByEmail(String userEmail) {
        AuthorizedUser user = null;
        try {
            Connection myConnection = factory.newConnection();
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(SELECT_BY_EMAIL_SQL);
                query.setString(1, userEmail);
                ResultSet result = query.executeQuery();
                while (result.next()) {
                    user = createUser(result);
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return user;
    }
}
